
package nienluan1;

import java.io.FileReader;
//Apache POI imports
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import java.io.FileOutputStream;
import java.io.IOException;
import nu.xom.Elements;

//XOM imports
import nu.xom.Builder;
import nu.xom.Document;
import nu.xom.Element;
import nu.xom.ParsingException;
import xulychuoi.*;
public class NienLuan1 {
	 public static void main(String argv[]) {

	try {
		int re_nrow_tmp=0;
		int nrow=0;//so dong
		int nrow_tmp=0;
		HSSFRow[] dong = new HSSFRow[100]; //mang ten cac dong
		String tmp = null; //bien tam chua noi dung the
                
		//tao file excel va header
		FileOutputStream fileOut = new FileOutputStream("excel-convert.xls");
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet worksheet = workbook.createSheet("TLI");
		HSSFFont fontheader = workbook.createFont();
		fontheader.setFontHeightInPoints((short)10);
		fontheader.setColor(HSSFColor.WHITE.index);

		
		// tao mot trang trong file excel				
		HSSFRow rowheader = worksheet.createRow((short) 0);
		worksheet.setColumnWidth(0, 5000);
		worksheet.setColumnWidth(1, 5000);
		worksheet.setColumnWidth(2, 5000);
		worksheet.setColumnWidth(3, 5000);
		worksheet.setColumnWidth(4, 5000);
		worksheet.setColumnWidth(5, 5000);
		worksheet.setColumnWidth(6, 5000);
		

		HSSFCell cellA1 = rowheader.createCell((short) 0);
		cellA1.setCellValue("Comman");
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
		cellStyle.setFillPattern(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setFont(fontheader);
		cellA1.setCellStyle(cellStyle);

		
		HSSFCell cellB1 = rowheader.createCell((short) 1);
		cellB1.setCellValue("Description");
		cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
		cellStyle.setFillPattern(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setFont(fontheader);
		cellB1.setCellStyle(cellStyle);
		
		HSSFCell cellC1 = rowheader.createCell((short) 2);
		cellC1.setCellValue("Parameter");
		cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
		cellStyle.setFillPattern(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setFont(fontheader);
		cellC1.setCellStyle(cellStyle);
		
		HSSFCell cellD1 = rowheader.createCell((short) 3);
		cellD1.setCellValue("Long Description");
		cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
		cellStyle.setFillPattern(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setFont(fontheader);
		cellD1.setCellStyle(cellStyle);
		
		HSSFCell cellE1 = rowheader.createCell((short) 4);
		cellE1.setCellValue("Range");
		cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
		cellStyle.setFillPattern(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setFont(fontheader);
		cellE1.setCellStyle(cellStyle);
		
		HSSFCell cellF1 = rowheader.createCell((short) 5);
		cellF1.setCellValue("Default Value");
		cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
		cellStyle.setFillPattern(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setFont(fontheader);
		cellF1.setCellStyle(cellStyle);
		
		HSSFCell cellG1 = rowheader.createCell((short) 6);
		cellG1.setCellValue("Retrieval Mechanism");
		cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
		cellStyle.setFillPattern(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setFont(fontheader);
		cellG1.setCellStyle(cellStyle);
//ket thuc viec tao header
		
		
		//load mot file xml vao
		FileReader file = new FileReader("test.xml");
		Builder xml = new nu.xom.Builder();
		Document doc = xml.build(file);
		String.valueOf(doc).replaceAll("&ldquo", "“");  
		String.valueOf(doc).replaceAll("&rdquo", "”");
		
		
		//lay nut root
		Element root = doc.getRootElement();
		
		//lay tat ca cac con la desripsion cua file nguon
		Elements des = root.getChildElements();
		
		//duyet qua tat ca cac con trong root, chi lay description
		for(int k=1; k<des.size()-1; k++){
			
			//lay tung the con desripsion theo thu tu k
			Element de =des.get(k); 
			
			//tu moi desripsion con lay ra tat ca cac the block con trong desripsion do
			Elements blos =de.getChildElements();
			
			//lap qua tat ca cac block vua thu duoc
			for(int i=1; i<blos.size(); i++){
				
				//lay the block theo thu tu i
				Element blo =blos.get(i);
				
				//xu ly bloc thu 1(dac biet)command
				if(i==1){
					Elements pars =blo.getChildElements(); // lay cac thẻ con trong block
					nrow_tmp=pars.size()-1;
					//bat dau lay the para dau tien bo label nen j=1
					for(int j=1;j<=nrow_tmp;j++){
						Element par = pars.get(j);
						tmp = par.getValue();
						tmp=xulychuoi.getCommand(tmp);
						nrow++;
						dong[nrow]=worksheet.createRow(nrow);
						dong[nrow].createCell(0).setCellValue(tmp);	
					}
				}//ket thuc lay Command
				
				
				//lay description
				if(i==2){
					Elements par2s= blo.getChildElements();
					for(int j=par2s.size()-1; j>0; j--){ // bỏ thẻ label
						Element par2 = par2s.get(j);
						tmp = par2.getValue();
						tmp=xulychuoi.getDescription(tmp);
						dong[nrow-j+1].createCell(1).setCellValue(tmp);
		//				System.out.println(nrow-j+1);
					}
				}   
				
				//lấy block so 7
				if(i==7){
					int tmp_command=nrow_tmp;
					Elements subblocks =blo.getChildElements();
					for(int j=2;j<subblocks.size();j++){
						tmp_command--;
						Element subblock =subblocks.get(j);
						Element label = subblock.getFirstChildElement("label");
						Element para = subblock.getFirstChildElement("para");
						tmp= para.getValue();
						tmp=xulychuoi.getDefault(tmp);
						if(j<=nrow_tmp+1){
							dong[nrow-tmp_command].createCell(2).setCellValue(label.getValue());
							dong[nrow-tmp_command].createCell(5).setCellValue(tmp);
				//			dong[nrow-tmp_command].createCell(5).setCellValue(xulychuoi.getDefault(tmp));
						}
						else{
							nrow++;
							dong[nrow]=worksheet.createRow(nrow);
							dong[nrow].createCell(2).setCellValue(label.getValue());
							dong[nrow].createCell(5).setCellValue(tmp);
				//			dong[nrow].createCell(5).setCellValue(xulychuoi.getDefault(tmp));
						}
						
					
					} 
					
				}

			}
		}
		workbook.write(fileOut);
		fileOut.flush();
		fileOut.close();
        } 
	catch (ParsingException | IOException ex) {
        }
    } // đóng main
} // đóng class
